package com.enniswu.weitter.data.local;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.util.Log;

import com.enniswu.weitter.Application;
import com.enniswu.weitter.entity.Post;
import com.enniswu.weitter.entity.User;

import java.util.ArrayList;
import java.util.List;

public class DBSupport extends SQLiteOpenHelper {

    private static final int VERSION = 1;
    private static volatile DBSupport instance;

    public static DBSupport getInstance() {
        if (instance == null) {
            synchronized (DBSupport.class) {
                if (instance == null) {
                    instance = new DBSupport();
                }
            }
        }
        return instance;
    }

    private User currentUser;

    private static final String CREATE_USER = "create table User ("
            + "id integer primary key, "
            + "portrait text, "
            + "name text, "
            + "gender integer check(gender=0 or gender=1), "
            + "synopsis text)";

    private static final String CREATE_POST = "create table Post ("
            + "id integer primary key, "
            + "context text not null, "
            + "createTime date not null, "
            + "albumCount integer not null, "
            + "commentCount integer not null, "
            + "type integer check(type=0 or type=1), "
            + "targetId integer not null, "
            + "userId integer not null)";

    private static final String CREATE_USER_FOLLOW = "create table User_Follow ("
            + "userId integer, "
            + "followerId integer, "
            + "primary key (userId, followerId), "
            + "foreign key (userId) references User(id), "
            + "foreign key (followerId) references User(id)"
            + ")";

    private static final String CREATE_POST_ALBUM = "create table Post_Album ("
            + "postId integer, "
            + "url text, "
            + "primary key (postId, url), "
            + "foreign key (postId) references Post(id)"
            + ")";

    private static final String CREATE_CHAT = "create table Chat ("
            + "id integer primary key, "
            + "fromId integer not null, "
            + "toId integer not null, "
            + "createTime date not null, "
            + "context text not null, "
            + "foreign key (fromId) references User(id), "
            + "foreign key (toId) references User(id)"
            + ")";

    private DBSupport() {
        super(Application.getContext(), "Weitter.db", null, VERSION);
        currentUser = findUser(SPSupport.getInstance().getId(), true);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if(!db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER);
        db.execSQL(CREATE_POST);
        db.execSQL(CREATE_USER_FOLLOW);
        db.execSQL(CREATE_POST_ALBUM);
        db.execSQL(CREATE_CHAT);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public boolean setCurrentUser(User currentUser) {
        this.currentUser = currentUser;
        SPSupport.getInstance().putId(currentUser.getId());
        return insertOrUpdate(currentUser);
    }

    @Nullable
    public User getCurrentUser() {
        return currentUser;
    }

    public boolean insertOrUpdate(User user) {
        if (user == null || user.getId() < 0)
            return false;
        ContentValues values = new ContentValues(5);
        values.put("id", user.getId());
        values.put("name", user.getName());
        values.put("portrait", user.getPortrait());
        values.put("gender", user.getGender());
        values.put("synopsis", user.getSynopsis());
        boolean result = insertOrUpdate(values, User.class);
        insertOrUpdate(user.getId(), user.getFollowing(), 0);
        insertOrUpdate(user.getId(), user.getFollowers(), 1);
        return result;
    }

    /**
     * @param type 0存关注，其他存粉丝
     */
    public void insertOrUpdate(int userId, List<Integer> followingOrFollow, int type) {
        if (userId < 0)
            return;
        deleteFollowingAndFollows(userId);
        if (followingOrFollow == null || followingOrFollow.isEmpty())
            return;
        String key1 = type == 0 ? "userId" : "followerId";
        String key2 = type == 0 ? "followerId" : "userId";
        if (type == 0) {
            for (int i : followingOrFollow) {
                ContentValues values = new ContentValues(followingOrFollow.size());
                values.put(key1, i);
                values.put(key2, userId);
                getWritableDatabase().insert("User_Follow", null, values);
            }
        }
    }

    public boolean insertOrUpdate(Post post) {
        if (post == null || post.getId() < 0)
            return false;
        ContentValues values = new ContentValues(8);
        values.put("id", post.getId());
        values.put("context", post.getContext());
        values.put("createTime", post.getCreateTime());
        values.put("albumCount", post.getAlbumCount());
        values.put("commentCount", post.getCommentCount());
        values.put("type", post.getType());
        values.put("targetId", post.getTargetId());
        values.put("userId", post.getUserId());
        boolean result =  insertOrUpdate(values, Post.class);
        if (post.getAlbumCount() > 0)
            insertOrUpdate(post.getId(), post.getAlbum());
        return result;
    }

    public void insertOrUpdate(int postId, List<String> album) {
        if (album == null || album.isEmpty())
            return;
        for (String image : album) {
            Cursor cursor = getWritableDatabase().query("Post_Album",
                    new String[]{"postId", "url"},
                    "postId = ? and url = ?",
                    new String[]{String.valueOf(postId), image},
                    null,
                    null,
                    null);
            if (cursor.getCount() == 0) {
                ContentValues values = new ContentValues(2);
                values.put("postId", postId);
                values.put("url", image);
                getWritableDatabase().insert("Post_Album", null, values);
            }
            cursor.close();
        }
    }

    public boolean insertOrUpdate(ContentValues values, Class classModel) {
        if (classModel != User.class && classModel != Post.class)
            return false;
        String table = classModel.getSimpleName();
        int id = values.getAsInteger("id");
        if (isExist(id, classModel)) {
            getWritableDatabase().update(table, values, "id = ?",
                    new String[]{String.valueOf(id)});
        } else {
            getWritableDatabase().insert(table, null, values);
        }
        return true;
    }

    public boolean isExist(int id, Class classModel) {
        String table;
        if (classModel.equals(User.class))
            table = "User";
        else if (classModel.equals(Post.class))
            table = "Post";
        else
            return false;
        Cursor cursor = getWritableDatabase().query(table, new String[]{"id"}, "id = ?",
                new String[]{String.valueOf(id)}, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        return count > 0;
    }

    @Nullable
    public User findUser(int id, boolean isEager) {
        Cursor cursor = getWritableDatabase().query("User",
                null,
                "id = ?", new String[]{String.valueOf(id)},
                null, null, null, "0,1");
        if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }
        User user = new User();
        user.setId(cursor.getInt(cursor.getColumnIndex("id")));
        user.setName(cursor.getString(cursor.getColumnIndex("name")));
        user.setPortrait(cursor.getString(cursor.getColumnIndex("portrait")));
        user.setGender(cursor.getInt(cursor.getColumnIndex("gender")));
        user.setSynopsis(cursor.getString(cursor.getColumnIndex("synopsis")));
        cursor.close();
        if (isEager) {
            user.setFollowing(findFollowingOrFollows(user.getId(), 0));
            user.setFollowers(findFollowingOrFollows(user.getId(), 1));
        }
        Log.d("数据库的用户", user.toString());
        return user;
    }

    /**
     * @param type 0查找关注，其他查找粉丝
     */
    @NonNull
    public List<Integer> findFollowingOrFollows(int userOrFollowerId, int type) {
        String columns = type == 0 ? "userId" : "followerId";
        String selection = type == 0 ? "followerId" : "userId";
        Cursor cursor = getWritableDatabase().query("User_Follow",
                new String[]{columns},
                selection + " = ?", new String[]{String.valueOf(userOrFollowerId)},
                null, null, null);
        List<Integer> follows = new ArrayList<>(cursor.getCount());
        if (cursor.moveToFirst()) {
            do {
                follows.add(cursor.getInt(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return follows;
    }

    @Nullable
    public Post findPost(int id) {
        Cursor cursor = getWritableDatabase().query("Post",
                null,
                "id = ?", new String[]{String.valueOf(id)},
                null, null, null, "0,1");
        if (!cursor.moveToFirst()) {
            cursor.close();
            return null;
        }
        Post post = new Post();
        post.setId(cursor.getInt(cursor.getColumnIndex("id")));
        post.setContext(cursor.getString(cursor.getColumnIndex("context")));
        post.setCreateTime(cursor.getString(cursor.getColumnIndex("createTime")));
        post.setAlbumCount(cursor.getInt(cursor.getColumnIndex("albumCount")));
        post.setCommentCount(cursor.getInt(cursor.getColumnIndex("commentCount")));
        post.setType(cursor.getInt(cursor.getColumnIndex("type")));
        post.setTargetId(cursor.getInt(cursor.getColumnIndex("targetId")));
        post.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
        cursor.close();
        if (post.getAlbumCount() > 0)
            post.setAlbum(findAlbum(post.getId()));
        Log.d("数据库的帖子", post.toString());
        return post;
    }

    @NonNull
    public List<String> findAlbum(int postId) {
        Cursor cursor = getWritableDatabase().query("Post_Album",
                new String[]{"url"},
                "postId = ?", new String[]{String.valueOf(postId)},
                null, null, null);
        List<String> album = new ArrayList<>(cursor.getCount());
        if (cursor.moveToFirst()) {
            do {
                album.add(cursor.getString(0));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return album;
    }

    @NonNull
    public List<Post> findAll(int type) {
        Cursor cursor = getWritableDatabase().query("Post",
                null,
                "type = ?", new String[]{String.valueOf(type)},
                null, null, null);
        List<Post> posts = new ArrayList<>(cursor.getCount());
        if (cursor.moveToFirst()) {
            do {
                Post post = new Post();
                post.setId(cursor.getInt(cursor.getColumnIndex("id")));
                post.setContext(cursor.getString(cursor.getColumnIndex("context")));
                post.setCreateTime(cursor.getString(cursor.getColumnIndex("createTime")));
                post.setAlbumCount(cursor.getInt(cursor.getColumnIndex("albumCount")));
                post.setCommentCount(cursor.getInt(cursor.getColumnIndex("commentCount")));
                post.setType(cursor.getInt(cursor.getColumnIndex("type")));
                post.setTargetId(cursor.getInt(cursor.getColumnIndex("targetId")));
                post.setUserId(cursor.getInt(cursor.getColumnIndex("userId")));
                posts.add(post);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return posts;
    }

    public int deleteAll(int type) {
        return getWritableDatabase().delete("Post", "type = ?"
                , new String[] {String.valueOf(type)});
    }

    public int deletePost(int postId, boolean isEager) {
        if (isEager)
            deleteAlbum(postId);
        return getWritableDatabase().delete("Post", "id = ?"
                , new String[] {String.valueOf(postId)});
    }

    public int deleteAlbum(int postId) {
        return getWritableDatabase().delete("Post_Album", "postId = ?"
                , new String[] {String.valueOf(postId)});
    }

    public int deleteUser(int userId, boolean isEager) {
        if (isEager)
            deleteFollowingAndFollows(userId);
        return getWritableDatabase().delete("User", "id = ?"
                , new String[] {String.valueOf(userId)});
    }

    public void deleteFollowingAndFollows(int userId) {
        SQLiteDatabase db = getWritableDatabase();
        String[] id = new String[] {String.valueOf(userId)};
        db.delete("User_Follow", "userId = ?", id);
        db.delete("User_Follow", "followerId = ?", id);
    }
}
